package com.zsrt.javaweb;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.zsrt.javaweb.bbu.department.dao.DepartmentDao;
import com.zsrt.javaweb.bbu.department.entity.DepartmentEntity;

public class JdbcTest {
	
	
	private ApplicationContext  ac=null;
	private JdbcTemplate  jdbcTemplate = null;
	
	private DepartmentDao dao = null;
	
	
	{
		ac= new ClassPathXmlApplicationContext("applicationContext.xml");
		jdbcTemplate= (JdbcTemplate) ac.getBean("jdbcTemplate");
		dao = ac.getBean(DepartmentDao.class);
	}
	
	
	@Test
	public void findByDao() throws Exception {
		System.out.println(dao.findById(10));
	}
	
	/**
	 * 批量新增
	 * @throws Exception
	 */
	@Test
	public void add() throws Exception {
		String sql="insert into bbu_department_t(id,department_name) values(?,?)";
		
		// Object数组的集合
		List<Object[]> argsList = new ArrayList<Object[]>();
		
		argsList.add(new Object[]{10,"cai-wu-bu"});
		argsList.add(new Object[]{20,"yan-fa-bu"});
		argsList.add(new Object[]{30,"jiao-wu-bu"});
		
		jdbcTemplate.batchUpdate(sql,argsList);
	}
	
	
	/**
	 * 把财务部修改为财神部
	 * @throws Exception
	 */
	@Test
	public void update() throws Exception {
		String sql="update bbu_department_t set department_name=? where id=?";
		jdbcTemplate.update(sql, "cai-shen-bu",10);
	}
	
	/**
	 * 根据id查找1个部门
	 * jdbcTemplate不支持级联属性的映射 ORM框架才支持级联属性的映射
	 * @throws Exception
	 */
	@Test
	public void findById() throws Exception {
		String sql = "select id, department_name as departmentName from bbu_department_t where id =?";
		
		//行映射   数据库中的1行记录对应java中的一个对象  如何映射结果集中的行 常用的实现类为BeanPropertyRowMapper
		//通过别名实现列和属性的映射[一一对应] department_name as departmentName
		RowMapper<DepartmentEntity> rowMapper =new BeanPropertyRowMapper<DepartmentEntity>(DepartmentEntity.class);
		DepartmentEntity entity= jdbcTemplate.queryForObject(sql, rowMapper, 10);
		System.out.println(entity);
	}
	
	/**
	 * 查找所有的部门
	 * @throws Exception
	 */
	@Test
	public void findAll() throws Exception {
		String sql = "select id, department_name as departmentName from bbu_department_t";
		
		//行映射   数据库中的1行记录对应java中的一个对象  如何映射结果集中的行 常用的实现类为BeanPropertyRowMapper
		//通过别名实现列和属性的映射[一一对应]
		RowMapper<DepartmentEntity> rowMapper =new BeanPropertyRowMapper<DepartmentEntity>(DepartmentEntity.class);
		List<DepartmentEntity> list= jdbcTemplate.query(sql, rowMapper);
		System.out.println(list);
	}
	
	
	
	/**
	 * 获取单个列的值 一般做统计
	 * @throws Exception
	 */
	@Test
	public void count() throws Exception {
		
		String sql ="select count(id) from bbu_department_t";
		Long count = jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(count);
		
	}

	@Test
	public void testDataSource() throws SQLException {
		
		DataSource dataSource = ac.getBean(DataSource.class);
		
		Connection connection = dataSource.getConnection();
		
		System.out.println(connection);
	}

}
